using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAO : DAO
	{

		public static ContratoVO getContrato(int? idContrato)
		{
			ContratoVO contratoVO = null;
			List<ContratoVO> listContratoVOs = getContratos(idContrato, null, null, null, null, null, null, null, null, null);
			if (listContratoVOs != null && listContratoVOs.Count > 0)
			{
				contratoVO = listContratoVOs[0];
			}
			return contratoVO;
		}

		public static List<ContratoVO> getContratos()
		{
			return getContratos(null, null, null, null, null, null, null, null, null, null);
		}

		public static List<ContratoVO> getContratos(int? idContrato)
		{
			return getContratos(idContrato, null, null, null, null, null, null, null, null, null);
		}

		public static List<ContratoVO> getContratos(int? idContrato, int? idFilial, int? idFormaPgto, int? idCCusto, int? idCClucro, int? idFornecedor, int? idCliente, string desc, bool? natureza, string nrDoc)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT ctt.idContrato as ctt_idContrato, ctt.idFilial as ctt_idFilial, fil.filial as fil_filial, ctt.desc as ctt_desc, ctt.vcto as ctt_vcto, ctt.natureza as ctt_natureza, ctt.parcelas as ctt_parcelas, ctt.intervalo as ctt_intervalo, ctt.valor as ctt_valor, ctt.nrDoc as ctt_nrDoc, ctt.separador as ctt_separador, ctt.incremento as ctt_incremento, ctt.idFormaPgto as ctt_idFormaPgto, fpg.formaPgto as fpg_formaPgto, ctt.idCCusto as ctt_idCCusto, cct.cCusto as cct_cCusto, ctt.idCClucro as ctt_idCClucro, clc.cLucro as clc_cLucro, ctt.idFornecedor as ctt_idFornecedor, ctt.idCliente as ctt_idCliente, ctt.Cadastro as ctt_Cadastro, ctt.CadastradoPor as ctt_CadastradoPor, ctt.Edicao as ctt_Edicao, ctt.EditadoPor as ctt_EditadoPor  ");
			query.AppendLine("FROM contrato as ctt LEFT OUTER JOIN filial as fil on ctt.idFilial=fil.idFilial  LEFT OUTER JOIN formapgto as fpg on ctt.idFormaPgto=fpg.idFormaPgto  LEFT OUTER JOIN ccusto as cct on ctt.idCCusto=cct.idCCusto  LEFT OUTER JOIN clucro as clc on ctt.idCClucro=clc.idCLucro  LEFT OUTER JOIN fornecedor as frn on ctt.idFornecedor=frn.idFornecedor  LEFT OUTER JOIN cliente as cli on ctt.idCliente=cli.idCliente  ");
			if (idContrato != null)
			{
				query.AppendLine(where + "ctt.idContrato=@idContrato ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "ctt.idFilial=@idFilial ");
				where = "AND ";
			}
			if (desc != null)
			{
				query.AppendLine(where + "ctt.desc=@desc ");
				where = "AND ";
			}
			if (natureza != null)
			{
				query.AppendLine(where + "ctt.natureza=@natureza ");
				where = "AND ";
			}
			if (nrDoc != null)
			{
				query.AppendLine(where + "ctt.nrDoc=@nrDoc ");
				where = "AND ";
			}
			if (idFormaPgto != null)
			{
				query.AppendLine(where + "ctt.idFormaPgto=@idFormaPgto ");
				where = "AND ";
			}
			if (idCCusto != null)
			{
				query.AppendLine(where + "ctt.idCCusto=@idCCusto ");
				where = "AND ";
			}
			if (idCClucro != null)
			{
				query.AppendLine(where + "ctt.idCClucro=@idCClucro ");
				where = "AND ";
			}
			if (idFornecedor != null)
			{
				query.AppendLine(where + "ctt.idFornecedor=@idFornecedor ");
				where = "AND ";
			}
			if (idCliente != null)
			{
				query.AppendLine(where + "ctt.idCliente=@idCliente ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY ctt.idContrato ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idContrato!= null) cmd.Parameters.Add(getParameter(cmd, "@idContrato", idContrato));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (desc!= null) cmd.Parameters.Add(getParameter(cmd, "@desc", desc));
			if (natureza!= null) cmd.Parameters.Add(getParameter(cmd, "@natureza", natureza.Value?"S":"N"));
			if (nrDoc!= null) cmd.Parameters.Add(getParameter(cmd, "@nrDoc", nrDoc));
			if (idFormaPgto!= null) cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", idFormaPgto));
			if (idCCusto!= null) cmd.Parameters.Add(getParameter(cmd, "@idCCusto", idCCusto));
			if (idCClucro!= null) cmd.Parameters.Add(getParameter(cmd, "@idCClucro", idCClucro));
			if (idFornecedor!= null) cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", idFornecedor));
			if (idCliente!= null) cmd.Parameters.Add(getParameter(cmd, "@idCliente", idCliente));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ContratoVO> listContrato = new List<ContratoVO>();

				while (reader.Read())
				{
					ContratoVO contratoVO = ContratoVO.CreateVO();
					contratoVO.idContrato = reader.GetInt32(reader.GetOrdinal("ctt_idContrato"));
					contratoVO.idFilial = reader.GetInt32(reader.GetOrdinal("ctt_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						contratoVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					contratoVO.desc = reader.GetString(reader.GetOrdinal("ctt_desc"));
					contratoVO.vcto = reader.GetDateTime(reader.GetOrdinal("ctt_vcto"));
					contratoVO.natureza = reader.GetString(reader.GetOrdinal("ctt_natureza")).Equals("S");
					contratoVO.parcelas = reader.GetInt32(reader.GetOrdinal("ctt_parcelas"));
					contratoVO.intervalo = reader.GetInt32(reader.GetOrdinal("ctt_intervalo"));
					contratoVO.valor = reader.GetDecimal(reader.GetOrdinal("ctt_valor"));
					contratoVO.nrDoc = reader.GetString(reader.GetOrdinal("ctt_nrDoc"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_separador")))
						contratoVO.separador = reader.GetString(reader.GetOrdinal("ctt_separador"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_incremento")))
						contratoVO.incremento = reader.GetInt32(reader.GetOrdinal("ctt_incremento"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_idFormaPgto")))
						contratoVO.idFormaPgto = reader.GetInt32(reader.GetOrdinal("ctt_idFormaPgto"));
					if (!reader.IsDBNull(reader.GetOrdinal("fpg_formaPgto")))
						contratoVO.idFormaPgtoVO.formaPgto = reader.GetString(reader.GetOrdinal("fpg_formaPgto"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_idCCusto")))
						contratoVO.idCCusto = reader.GetInt32(reader.GetOrdinal("ctt_idCCusto"));
					if (!reader.IsDBNull(reader.GetOrdinal("cct_cCusto")))
						contratoVO.idCCustoVO.cCusto = reader.GetString(reader.GetOrdinal("cct_cCusto"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_idCClucro")))
						contratoVO.idCClucro = reader.GetInt32(reader.GetOrdinal("ctt_idCClucro"));
					if (!reader.IsDBNull(reader.GetOrdinal("clc_cLucro")))
						contratoVO.idCClucroVO.cLucro = reader.GetString(reader.GetOrdinal("clc_cLucro"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_idFornecedor")))
						contratoVO.idFornecedor = reader.GetInt32(reader.GetOrdinal("ctt_idFornecedor"));
					if (!reader.IsDBNull(reader.GetOrdinal("ctt_idCliente")))
						contratoVO.idCliente = reader.GetInt32(reader.GetOrdinal("ctt_idCliente"));
					contratoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("ctt_Cadastro"));
					contratoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("ctt_CadastradoPor"));
					contratoVO.edicao = reader.GetDateTime(reader.GetOrdinal("ctt_Edicao"));
					contratoVO.editadoPor = reader.GetString(reader.GetOrdinal("ctt_EditadoPor"));


					listContrato.Add(contratoVO);
				}
				reader.Close();
				return listContrato;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public static void setContrato(ContratoVO contratoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE contrato ");
			query.AppendLine("SET idFilial=@idFilial, desc=@desc, vcto=@vcto, natureza=@natureza, parcelas=@parcelas, intervalo=@intervalo, valor=@valor, nrDoc=@nrDoc, separador=@separador, incremento=@incremento, idFormaPgto=@idFormaPgto, idCCusto=@idCCusto, idCClucro=@idCClucro, idFornecedor=@idFornecedor, idCliente=@idCliente,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idContrato=@idContrato ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idContrato", contratoVO.idContrato));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", contratoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@desc", contratoVO.desc));
			cmd.Parameters.Add(getParameter(cmd, "@vcto", contratoVO.vcto));
			cmd.Parameters.Add(getParameter(cmd, "@natureza", (contratoVO.natureza==null?" ":(contratoVO.natureza.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@parcelas", contratoVO.parcelas));
			cmd.Parameters.Add(getParameter(cmd, "@intervalo", contratoVO.intervalo));
			cmd.Parameters.Add(getParameter(cmd, "@valor", contratoVO.valor));
			cmd.Parameters.Add(getParameter(cmd, "@nrDoc", contratoVO.nrDoc));
			if (contratoVO.separador == null)
				cmd.Parameters.Add(getParameter(cmd, "@separador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@separador", contratoVO.separador));
			if (contratoVO.incremento == null)
				cmd.Parameters.Add(getParameter(cmd, "@incremento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@incremento", contratoVO.incremento));
			if (contratoVO.idFormaPgto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", contratoVO.idFormaPgto));
			if (contratoVO.idCCusto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", contratoVO.idCCusto));
			if (contratoVO.idCClucro == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCClucro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCClucro", contratoVO.idCClucro));
			if (contratoVO.idFornecedor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", contratoVO.idFornecedor));
			if (contratoVO.idCliente == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", contratoVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", contratoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", contratoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", contratoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", contratoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public static long newContrato(ContratoVO contratoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO contrato ");
			query.AppendLine("  ( idFilial, desc, vcto, natureza, parcelas, intervalo, valor, nrDoc, separador, incremento, idFormaPgto, idCCusto, idCClucro, idFornecedor, idCliente, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @idFilial, @desc, @vcto, @natureza, @parcelas, @intervalo, @valor, @nrDoc, @separador, @incremento, @idFormaPgto, @idCCusto, @idCClucro, @idFornecedor, @idCliente, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", contratoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@desc", contratoVO.desc));
			cmd.Parameters.Add(getParameter(cmd, "@vcto", contratoVO.vcto));
			cmd.Parameters.Add(getParameter(cmd, "@natureza", (contratoVO.natureza==null?" ":(contratoVO.natureza.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@parcelas", contratoVO.parcelas));
			cmd.Parameters.Add(getParameter(cmd, "@intervalo", contratoVO.intervalo));
			cmd.Parameters.Add(getParameter(cmd, "@valor", contratoVO.valor));
			cmd.Parameters.Add(getParameter(cmd, "@nrDoc", contratoVO.nrDoc));
			if (contratoVO.separador == null)
				cmd.Parameters.Add(getParameter(cmd, "@separador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@separador", contratoVO.separador));
			if (contratoVO.incremento == null)
				cmd.Parameters.Add(getParameter(cmd, "@incremento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@incremento", contratoVO.incremento));
			if (contratoVO.idFormaPgto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFormaPgto", contratoVO.idFormaPgto));
			if (contratoVO.idCCusto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCCusto", contratoVO.idCCusto));
			if (contratoVO.idCClucro == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCClucro", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCClucro", contratoVO.idCClucro));
			if (contratoVO.idFornecedor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idFornecedor", contratoVO.idFornecedor));
			if (contratoVO.idCliente == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCliente", contratoVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", contratoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", contratoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", contratoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", contratoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('contrato') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public static void delContrato(ContratoVO contratoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM contrato ");
			query.AppendLine("WHERE idContrato=@idContrato ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idContrato", contratoVO.idContrato));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public static int getCountContratoxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM contrato WHERE IdFilial=@IdFilial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public static int getCountContratoxIdFormaPgto(int? idFormaPgto)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFormaPgto) as ocorrencias FROM contrato WHERE IdFormaPgto=@IdFormaPgto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFormaPgto", idFormaPgto));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public static int getCountContratoxIdCCusto(int? idCCusto)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCCusto) as ocorrencias FROM contrato WHERE IdCCusto=@IdCCusto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCCusto", idCCusto));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public static int getCountContratoxIdCClucro(int? idCClucro)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCClucro) as ocorrencias FROM contrato WHERE IdCClucro=@IdCClucro ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCClucro", idCClucro));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public static int getCountContratoxIdFornecedor(int? idFornecedor)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFornecedor) as ocorrencias FROM contrato WHERE IdFornecedor=@IdFornecedor ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFornecedor", idFornecedor));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public static int getCountContratoxIdCliente(int? idCliente)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCliente) as ocorrencias FROM contrato WHERE IdCliente=@IdCliente ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCliente", idCliente));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
